﻿using Furion.FriendlyException;
using Microsoft.AspNetCore.Http;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using Spire.Xls;
using System.Data;
using System.Drawing.Imaging;
using System.Reflection;
using ICell = NPOI.SS.UserModel.ICell;

namespace iWare.Wms.Core
{
    /// <summary>
    /// ExcelHelper导入
    /// </summary>
    public class ExcelOperation
    {
        /// <summary>
        /// 以时间命名
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public static string GetFileName(string name)
        {
            string FileName = $"{name}{DateTime.Now:yyyyMMddhhmmss}.xls";
            return FileName;
        }

        /// <summary>
        /// 以时间命名不带后缀名
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public static string GetFileNameEmpty(string name)
        {
            string FileName = $"{name}{DateTime.Now:yyyyMMddhhmmss}";
            return FileName;
        }

        /// <summary>
        /// 导出 返回具体的路径文件
        /// </summary>
        /// <param name="dtDictionary">支持多个sheet导入</param>
        /// <param name="FileName"> xxx.xlsx</param>
        /// <param name="ProjectPath">路径可为null</param>
        /// <param name="ExportPath"> 默认  "DownloadExcel\\"</param>
        /// <returns></returns>
        public static string ExportExcel(Dictionary<string, DataTable> dtDictionary, string FileName, string ProjectPath = "", string ExportPath = "")
        {
            if (dtDictionary is null || dtDictionary.Count == 0) return null;
            if (string.IsNullOrWhiteSpace(FileName)) FileName = Guid.NewGuid().ToString() + ".xlsx";
            if (string.IsNullOrWhiteSpace(ProjectPath)) ProjectPath = ExcelOperation.ProjectPath();
            if (string.IsNullOrWhiteSpace(ExportPath)) ExportPath = "DownloadExcel\\";

            Excel excel = new();
            foreach (var dt in dtDictionary)
            {
                if (dt.Value != null && dt.Value.Rows.Count > 0)
                {
                    excel.CreateSheet(dt.Value.Columns, dt.Key);
                    foreach (DataRow row in dt.Value.Rows)
                    {
                        excel.SetRowValue(row);
                    }
                }
            }
            string filePath = ProjectPath + ExportPath;
            if (!System.IO.Directory.Exists(filePath)) Directory.CreateDirectory(filePath);
            filePath = filePath + FileName;
            excel.SaveAs(filePath);
            // return "\\" + ExportPath + FileName;
            return filePath;
        }

        /// <summary>
        /// 导出 返回个steam流
        /// </summary>
        /// <param name="dtDictionary">支持多个sheet导入</param>
        /// <param name="FileName"> xxx.xlsx</param>
        /// <param name="ProjectPath">路径可为null</param>
        /// <param name="ExportPath"> 默认  "DownloadExcel\\"</param>
        /// <returns></returns>
        public static Stream ExportExcel(Dictionary<string, DataTable> dtDictionary)
        {
            if (dtDictionary is null || dtDictionary.Count == 0) return null;
            //  if (string.IsNullOrWhiteSpace(FileName)) FileName = Guid.NewGuid().ToString() + ".xlsx";
            //if (string.IsNullOrWhiteSpace(ProjectPath)) ProjectPath = ExcelOperation.ProjectPath();
            //if (string.IsNullOrWhiteSpace(ExportPath)) ExportPath = "DownloadExcel\\";

            Excel excel = new();
            foreach (var dt in dtDictionary)
            {
                if (dt.Value != null && dt.Value.Rows.Count > 0)
                {
                    excel.CreateSheet(dt.Value.Columns, dt.Key);
                    foreach (DataRow row in dt.Value.Rows)
                    {
                        excel.SetRowValue(row);
                    }
                }
            }
            MemoryStream memoryStream = excel.SaveAsMemoryStream();
            return memoryStream;
        }
        /// <summary>
        /// 获取当前项目的目录
        /// </summary>
        /// <returns></returns>
        public static string ProjectPath()
        {
            string Paths = AppDomain.CurrentDomain.BaseDirectory;
            return Paths;
        }

        /// <summary>
        /// 将excel导入到datatable
        /// </summary>
        /// <param name="filePath">excel路径</param>
        /// <param name="isColumnName">第一行是否是列名</param>
        /// <returns>返回datatable</returns>
        public static DataTable ExcelToDataTable(string filePath, bool isColumnName)
        {
            DataTable dataTable = null;
            FileStream fs = null;
            DataColumn column = null;
            DataRow dataRow = null;
            IWorkbook workbook = null;
            ISheet sheet = null;
            IRow row = null;
            ICell cell = null;
            int startRow = 0;
            try
            {
                using (fs = File.OpenRead(filePath))
                {

                    if (fs == null)
                    {
                        throw Oops.Oh("请上传事件文件");
                    }
                    long length = fs.Length;
                    if (length > 1024 * 1024 * 100) //200M
                    {
                        throw Oops.Oh("上传文件不能超过100M");
                    }
                    fs.Position = 0;
                    // 2007版本
                    if (filePath.ToLower().IndexOf(".xlsx") > 0)
                        workbook = new XSSFWorkbook(fs);
                    // 2003版本
                    else if (filePath.ToLower().IndexOf(".xls") > 0)
                        // workbook = new HSSFWorkbook(fs);
                        workbook = new XSSFWorkbook(fs);
                    else
                    {
                        throw Oops.Oh("请上传事件文件");
                    }

                    if (workbook != null)
                    {
                        sheet = workbook.GetSheetAt(0);//读取第一个sheet，当然也可以循环读取每个sheet
                        dataTable = new System.Data.DataTable();
                        if (sheet != null)
                        {
                            int rowCount = sheet.LastRowNum;//总行数
                            if (rowCount > 0)
                            {
                                IRow firstRow = sheet.GetRow(0);//第一行
                                int cellCount = firstRow.LastCellNum;//列数

                                //构建datatable的列
                                if (isColumnName)
                                {
                                    startRow = 1;//如果第一行是列名，则从第二行开始读取
                                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                    {
                                        cell = firstRow.GetCell(i);
                                        if (cell != null)
                                        {
                                            if (cell.StringCellValue != null)
                                            {
                                                column = new DataColumn(cell.StringCellValue);
                                                dataTable.Columns.Add(column);
                                            }
                                        }
                                    }
                                }
                                else
                                {
                                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                    {
                                        column = new DataColumn("column" + (i + 1));
                                        dataTable.Columns.Add(column);
                                    }
                                }

                                //填充行
                                for (int i = startRow; i <= rowCount; ++i)
                                {
                                    row = sheet.GetRow(i);
                                    if (row == null) continue;

                                    dataRow = dataTable.NewRow();
                                    for (int j = row.FirstCellNum; j < cellCount; ++j)
                                    {
                                        cell = row.GetCell(j);
                                        if (cell == null)
                                        {
                                            dataRow[j] = "";
                                        }
                                        else
                                        {
                                            //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)
                                            switch (cell.CellType)
                                            {
                                                case CellType.Blank:
                                                    dataRow[j] = "";
                                                    break;

                                                case CellType.Numeric:
                                                    short format = cell.CellStyle.DataFormat;
                                                    //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理
                                                    if (format == 14 || format == 31 || format == 57 || format == 58)
                                                    {
                                                        if (DateUtil.IsCellDateFormatted(cell))
                                                        {
                                                            dataRow[j] = cell.DateCellValue.Date.ToString();
                                                        }
                                                    }
                                                    else
                                                    {
                                                        dataRow[j] = cell.NumericCellValue;
                                                    }

                                                    break;

                                                case CellType.String:
                                                    dataRow[j] = cell.StringCellValue;
                                                    break;
                                            }
                                        }
                                        //为创建人赋值
                                        // dataRow[cellCount] = userad;
                                    }
                                    //去除空白行数据
                                    bool rowdataisnull = true;
                                    for (int k = row.FirstCellNum; k < cellCount - 1; ++k)
                                    {
                                        if (!string.IsNullOrEmpty(dataRow[k] as string))
                                        {
                                            rowdataisnull = false;
                                        }
                                    }
                                    if (!rowdataisnull)
                                    {
                                        dataTable.Rows.Add(dataRow);
                                    }
                                }
                            }
                        }
                    }
                }
                return dataTable;
            }
            catch (Exception ex)
            {
                if (fs != null)
                {
                    fs.Close();
                }
                throw Oops.Oh("错误：" + ex.Message);
            }
        }

        /// <summary>
        /// 以文件流IFormFile方式导入到DataTable
        /// </summary>
        /// <param name="file"></param>
        /// <returns></returns>
        public static DataTable TaskFileImport(IFormFile file)
        {
            string[] fileExcels = ".xls,.xlsx".Split(',');

            if (file == null)
            {
                throw Oops.Oh("请上传事件文件");
            }
            var fileExtension = Path.GetExtension(file.FileName);
            if (fileExtension == null)
            {
                throw Oops.Oh("文件无后缀信息");
            }
            long length = file.Length;
            if (length > 1024 * 1024 * 100) //200M
            {
                throw Oops.Oh("上传文件不能超过100M");
            }
            if (!fileExcels.Contains(fileExtension))
            {
                throw Oops.Oh("上传文件只支持.xls,.xlsx");
            }

            string filePath = ExcelOperation.ProjectPath() + "DownloadExcel\\";
            if (!System.IO.Directory.Exists(filePath)) Directory.CreateDirectory(filePath);
            var saveName = filePath + $"{DateTime.Now:yyyyMMddhhmmss}" + (file.FileName);
            using (FileStream fs = File.Create(saveName))
            {
                file.CopyTo(fs);
            }
            var datatel = ExcelToDataTable(saveName, true);
            return datatel;
        }

        /// <summary>
        /// 将excel导入到datatable，指定sheetname
        /// </summary>
        /// <param name="filePath">excel路径</param>
        /// <param name="isColumnName">第一行是否是列名</param>
        /// <param name="sheetName">Excel的sheet名</param>
        /// <returns>返回datatable</returns>
        public static List<DataTable> ExcelToDataTableList(string filePath, bool isColumnName, string[] sheetName)
        {
            List<DataTable> dataTableList = null;
            FileStream fs = null;
            DataColumn column = null;
            DataRow dataRow = null;
            IWorkbook workbook = null;
            ISheet sheet = null;
            IRow row = null;
            ICell cell = null;
            int startRow = 0;
            try
            {
                using (fs = File.OpenRead(filePath))
                {
                    // 2007版本
                    if (filePath.IndexOf(".xlsx") > 0)
                        workbook = new XSSFWorkbook(fs);
                    // 2003版本
                    else if (filePath.IndexOf(".xls") > 0)
                        workbook = new HSSFWorkbook(fs);

                    if (workbook != null && sheetName != null && sheetName.Length > 0)
                    {
                        dataTableList = new List<DataTable>();
                        //string[] sheetName = { "工单", "工单材料", "杆件", "杆件工时", "配料单", "配料单详情", "物料主数据", "工艺", "工序", "工艺工序", "检验项" };
                        for (int w = 0; w < sheetName.Length; w++)
                        //for (int w = 0; w < workbook.NumberOfSheets; w++)
                        {
                            //sheet = workbook.GetSheetAt(w);//循环读取每个sheet(根据下标获取)
                            sheet = workbook.GetSheet(sheetName[w]);//循环读取每个sheet(根据名称获取)
                            DataTable dataTable = new();
                            if (sheet != null)
                            {
                                int rowCount = sheet.LastRowNum;//总行数
                                if (rowCount > 0)
                                {
                                    IRow firstRow = sheet.GetRow(0);//第一行
                                    int cellCount = firstRow.LastCellNum;//列数

                                    #region 构建datatable的列
                                    if (isColumnName)
                                    {
                                        startRow = 1;//如果第一行是列名，则从第二行开始读取
                                        for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                        {
                                            cell = firstRow.GetCell(i);
                                            if (cell != null)
                                            {
                                                if (cell.StringCellValue != null)
                                                {
                                                    column = new DataColumn(cell.StringCellValue);
                                                    dataTable.Columns.Add(column);
                                                }
                                            }
                                        }
                                    }
                                    else
                                    {
                                        for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                        {
                                            column = new DataColumn("column" + (i + 1));
                                            dataTable.Columns.Add(column);
                                        }
                                    }
                                    #endregion

                                    #region 填充行
                                    for (int i = startRow; i <= rowCount; ++i)
                                    {
                                        row = sheet.GetRow(i);
                                        if (row == null) continue;

                                        bool IsNull = true;//是否是空行
                                        dataRow = dataTable.NewRow();
                                        for (int j = row.FirstCellNum; j < cellCount; ++j)
                                        {
                                            cell = row.GetCell(j);
                                            if (cell == null)
                                            {
                                                dataRow[j] = "";
                                            }
                                            else
                                            {
                                                object value = null;
                                                //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)
                                                switch (cell.CellType)
                                                {
                                                    case CellType.Blank:
                                                        value = "";
                                                        break;
                                                    case CellType.Numeric:
                                                        short format = cell.CellStyle.DataFormat;
                                                        //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理
                                                        if (format == 14 || format == 31 || format == 57 || format == 58 || format == 176)
                                                            value = cell.DateCellValue;
                                                        else
                                                            value = Math.Round(cell.NumericCellValue, 2);//2位小数
                                                        break;
                                                    case CellType.String:
                                                        value = cell.StringCellValue;
                                                        break;
                                                    case CellType.Formula://公式
                                                        row.GetCell(j).SetCellType(CellType.String);//先改变公式为字符
                                                        value = cell.StringCellValue;
                                                        break;
                                                    default:
                                                        value = cell.StringCellValue;
                                                        break;
                                                }

                                                if (!string.IsNullOrWhiteSpace(value.ToString()))
                                                {
                                                    IsNull = false;
                                                    dataRow[j] = value;
                                                }
                                            }
                                        }
                                        if (!IsNull)
                                            dataTable.Rows.Add(dataRow);
                                    }
                                    #endregion
                                }
                                dataTableList.Add(dataTable);
                            }
                        }
                    }
                }
                return dataTableList;
            }
            catch (Exception)
            {
                if (fs != null)
                {
                    fs.Close();
                }
                return null;
            }
        }

        /// <summary>
        /// 格式化关键字集合
        /// </summary>
        /// <typeparam name="T">泛型对象</typeparam>
        /// <param name="t">关键字集对象</param>
        /// <returns></returns>
        public static Dictionary<string, string> GetProperties<T>(T t)
        {
            Dictionary<string, string> keywords = new();
            if (t == null)
            {
                return keywords;
            }
            System.Reflection.PropertyInfo[] properties = t.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);

            if (properties.Length <= 0)
            {
                return keywords;
            }
            foreach (System.Reflection.PropertyInfo item in properties)
            {
                string name = "{$" + item.Name + "}";
                object value = item.GetValue(t, null);
                if (item.PropertyType.IsValueType || item.PropertyType.Name.StartsWith("String"))
                {
                    keywords.Add(name, value.ToString());
                }
                else
                {
                    GetProperties(value);
                }
            }
            return keywords;
        }

        /// <summary>
        /// NPOI使用ShiftRows向excel插入行，并复制原有样式
        /// </summary>
        /// <param name="SavePath">模板文件，包含物理路径</param>
        /// <param name="list">数据集合</param>
        /// <param name="startRow">开始插入行索引</param>
        /// <param name="func">循环赋值的方法</param>
        public static void ShiftRows<T>(string SavePath, List<T> list, int startRow, Func<IRow, int, bool> func)//开始插入行索引
        {
            //创建Excel文件的对象
            FileStream fs = new(SavePath, FileMode.Open, FileAccess.ReadWrite);
            XSSFWorkbook workbook = new(fs);
            fs.Close();
            ISheet sheet = workbook.GetSheetAt(0);
            if (list != null && list.Count > 1)
            {
                //插入行
                sheet.ShiftRows(startRow, sheet.LastRowNum, list.Count - 1, true, false);
                var rowSource = sheet.GetRow(startRow - 1);
                var rowStyle = rowSource.RowStyle;//获取当前行样式
                for (int i = startRow; i < startRow + list.Count - 1; i++)
                {
                    var rowInsert = sheet.CreateRow(i);
                    if (rowStyle != null) rowInsert.RowStyle = rowStyle;
                    rowInsert.Height = rowSource.Height;
                    for (int col = 0; col < rowSource.LastCellNum; col++)
                    {
                        var cellsource = rowSource.GetCell(col);
                        var cellInsert = rowInsert.CreateCell(col);
                        var cellStyle = cellsource.CellStyle;
                        //设置单元格样式
                        if (cellStyle != null)
                            cellInsert.CellStyle = cellsource.CellStyle;
                    }
                }
            }
            //绑定数据
            for (int j = startRow; j < startRow + list.Count; j++)
            {
                //单元格赋值等其他代码
                // 标准写法 r.Cells[0].SetCellValue(j + 1);
            }
            FileStream success = new(SavePath, FileMode.Create);
            workbook.Write(success);
            success.Close();
            workbook.Close();
        }

        /// <summary>
        /// 数据替换
        /// </summary>
        /// <typeparam name="T">实体  替换默认为 {Name} </typeparam>
        /// <param name="entity"></param>
        /// <param name="FromSourcePath"></param>
        /// <param name="ToSourcePath"></param>
        /// <returns></returns>
        public static string ExcelReplace<T>(T entity, string FromSourcePath, string ToSourcePath)
        {
            FileStream fs = new(FromSourcePath, FileMode.Open, FileAccess.Read);
            IWorkbook workbook = WorkbookFactory.Create(fs);
            ISheet sheet = workbook.GetSheetAt(0);
            IRow firstRow = sheet.GetRow(0);
            int cellCount = firstRow.LastCellNum;
            int rowCount = sheet.LastRowNum;
            for (int i = 0; i < rowCount; i++)
            {
                IRow row = sheet.GetRow(i);
                for (int j = 0; j < cellCount; j++)
                {
                    NPOI.SS.UserModel.ICell cell = row.GetCell(j);
                    ReplaceExcelKey<T>(entity, cell);
                }
            }
            if (File.Exists(ToSourcePath)) File.Delete(ToSourcePath);
            var file = new FileStream(ToSourcePath, FileMode.Create);
            workbook.Write(file);
            file.Close();
            return ToSourcePath;
        }

        private static void ReplaceExcelKey<T>(T etity, NPOI.SS.UserModel.ICell cell)
        {
            Type entityType = typeof(T);
            PropertyInfo[] properties = entityType.GetProperties();
            if (cell != null)
            {
                var text = "";
                if (cell.CellType == CellType.String)//这⾥根据不同的类型进⾏不同的处理
                {
                    text = cell.ToString();
                }
                if (text != "")
                {
                    foreach (var p in properties)
                    {
                        string propteryName = "{" + p.Name + "}";
                        object value = p.GetValue(etity);
                        if (value == null)
                        {
                            value = "";
                        }
                        if (text.Contains(propteryName))
                        {
                            var v = value.ToString();
                            text = text.Replace(propteryName, v);
                        }
                        cell.SetCellValue(text);
                    }
                }

            }
        }

        /// <summary>
        /// excel转image
        /// </summary>
        /// <param name="filename"></param>
        /// <param name="imagepath"></param>
        public static string ChangeExcel2Image(string filename, string imagepath)
        {
            string filePath = ExcelOperation.ProjectPath() + "DownloadExcel\\";
            if (!System.IO.Directory.Exists(filePath)) Directory.CreateDirectory(filePath);
            var saveName = filePath + $"{DateTime.Now:yyyyMMddhhmmss}" + imagepath;
            Workbook workbook = new();
            workbook.LoadFromFile(filename);
            Worksheet sheet = workbook.Worksheets[0];
            //sheet.SaveToImage(imagepath); //图片后缀.bmp ,imagepath自己设置
            //                              //加载生成图表后的Excel文档
            //workbook.LoadFromFile("ColumnChart.xlsx");
            sheet.ToImage(0, 0, 0, 0).Save(saveName, ImageFormat.Jpeg);
            return saveName;
        }

        public static string ExcelRepleImages(string TagetPath, string imgpath)
        {
            string fileExtensionName = Path.GetExtension(TagetPath);
            if (fileExtensionName.ToLower() == ".xlsx")
            {
                return InsertImageToXLSXExcel(TagetPath, imgpath);
            }
            if (fileExtensionName.ToLower() == ".xls")
            {
                return InsertImageToXLSExcel(TagetPath, imgpath);
            }
            return "";
        }
        /// <summary>
        /// .xlsx后缀的Excel文件添加图片
        /// </summary>
        /// <param name="excelPath"></param>
        /// <param name="imgPath"></param>
        private static string InsertImageToXLSXExcel(string excelPath, string imgPath)
        {
            try
            {
                using FileStream fs = new(excelPath, FileMode.Open);//获取指定Excel文件流
                                                                               //创建工作簿
                XSSFWorkbook xssfworkbook = new(fs);
                //获取第一个工作表（下标从0起）
                XSSFSheet sheet = (XSSFSheet)xssfworkbook.GetSheet(xssfworkbook.GetSheetName(0));
                //获取指定图片的字节流
                byte[] bytes = System.IO.File.ReadAllBytes(imgPath);
                //将图片添加到工作簿中，返回值为该图片在工作表中的索引（从0开始）
                //图片所在工作簿索引理解：如果原Excel中没有图片，那执行下面的语句后，该图片为Excel中的第1张图片，其索引为0；
                //同理，如果原Excel中已经有1张图片，执行下面的语句后，该图片为Excel中的第2张图片，其索引为1；
                int pictureIdx = xssfworkbook.AddPicture(bytes, PictureType.PNG);
                //创建画布
                XSSFDrawing patriarch = (XSSFDrawing)sheet.CreateDrawingPatriarch();
                //设置图片坐标与大小
                //函数原型：XSSFClientAnchor(int dx1, int dy1, int dx2, int dy2, int col1, int row1, int col2, int row2)；
                //坐标(col1,row1)表示图片左上角所在单元格的位置，均从0开始，比如(5,2)表示(第六列，第三行),即F3；注意：图片左上角坐标与(col1,row1)单元格左上角坐标重合
                //坐标(col2,row2)表示图片右下角所在单元格的位置，均从0开始，比如(10,3)表示(第十一列，第四行),即K4；注意：图片右下角坐标与(col2,row2)单元格左上角坐标重合
                //坐标(dx1,dy1)表示图片左上角在单元格(col1,row1)基础上的偏移量(往右下方偏移)；(dx1，dy1)的最大值为(1023, 255),为一个单元格的大小
                //坐标(dx2,dy2)表示图片右下角在单元格(col2,row2)基础上的偏移量(往右下方偏移)；(dx2,dy2)的最大值为(1023, 255),为一个单元格的大小
                //注意：目前测试发现，对于.xlsx后缀的Excel文件，偏移量设置(dx1,dy1)(dx2,dy2)无效；只会对.xls生效
                XSSFClientAnchor anchor = new(0, 0, 20, 15, 0, 2, 2, 6);
                //正式在指定位置插入图片
                XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
                //创建一个新的Excel文件流，可以和原文件名不一样，
                //如果不一样，则会创建一个新的Excel文件；如果一样，则会覆盖原文件
                FileStream file = new("E:/图片3333333333.xlsx", FileMode.Create);
                //将已插入图片的Excel流写入新创建的Excel中
                xssfworkbook.Write(file);
                file.Close();
                //关闭工作簿
                xssfworkbook.Close();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return excelPath;
        }

        /// <summary>
        /// .xls后缀的Excel文件添加图片
        /// </summary>
        /// <param name="TagetPath"></param>
        /// <param name="imgpath"></param>
        private static string InsertImageToXLSExcel(string TagetPath, string imgpath)
        {
            FileStream file = new(TagetPath, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite);
            IWorkbook hssfworkbook = new HSSFWorkbook(file);

            System.Drawing.Image image = System.Drawing.Image.FromFile(imgpath);
            MemoryStream ms = new();
            image.Save(ms, System.Drawing.Imaging.ImageFormat.Png);
            //设置图片坐标与大小
            //函数原型：XSSFClientAnchor(int dx1, int dy1, int dx2, int dy2, int col1, int row1, int col2, int row2)；
            //坐标(col1,row1)表示图片左上角所在单元格的位置，均从0开始，比如(5,2)表示(第六列，第三行),即F3；注意：图片左上角坐标与(col1,row1)单元格左上角坐标重合
            //坐标(col2,row2)表示图片右下角所在单元格的位置，均从0开始，比如(10,3)表示(第十一列，第四行),即K4；注意：图片右下角坐标与(col2,row2)单元格左上角坐标重合
            //坐标(dx1,dy1)表示图片左上角在单元格(col1,row1)基础上的偏移量(往右下方偏移)；(dx1，dy1)的最大值为(1023, 255),为一个单元格的大小
            //坐标(dx2,dy2)表示图片右下角在单元格(col2,row2)基础上的偏移量(往右下方偏移)；(dx2,dy2)的最大值为(1023, 255),为一个单元格的大小
            //注意：目前测试发现，对于.xlsx后缀的Excel文件，偏移量设置(dx1,dy1)(dx2,dy2)无效；只会对.xls生效
            int idex1 = hssfworkbook.GetSheetIndex("领料单");
            hssfworkbook.SetSheetName(idex1, "领料单");

            FileStream fs2 = new("E:/图片操2222222作.xls", FileMode.Create, FileAccess.ReadWrite, FileShare.ReadWrite);
            hssfworkbook.Write(fs2);
            fs2.Close();
            image.Dispose();
            file.Close();
            return TagetPath;
        }
    }
}



